'use strict';
var moment = require('moment');

/**
 * List of business
 */
exports.add = function(req, res) {
    var data = {};
    data=req.body;
    var currentTime=moment().format('YYYY-MM-DD HH:mm:ss');
    data['time']=currentTime;
    if(JSON.stringify(data) != "{}"){
        var sql="(";
        var lastObj="";
        for (var prop in data){
            lastObj=prop;
        }
        for (var prop in data)
        {
            sql+=prop;
            if(prop!=lastObj){
                sql+=" , ";
            }else{
                sql+=") values (";
            }
        }
        for (var prop in data)
        {
            sql+="'"+data[prop]+"' ";
            if(prop!=lastObj){
                sql+=" , ";
            }else{
                sql+=") ";
            }
        }
        req.getConnection(function(err, conn) {
            if (err) {
                return res.send(401);
            } else {
                conn.query('insert into business '+sql, function(err,result) {
                    if (err) {
                        res.json(false);
                    } else {
                        res.json(true); 
                    }
                });
            }
        });
    }else{
        res.json(false);
    }
};

// 添加多条
exports.addMultiterm = function(req, res) {
    var dataStr,companyid;
    dataStr=req.body.dataStr;
    companyid=req.body.companyid;
    var data = JSON.parse(dataStr);
    var currentTime=moment().format('YYYY-MM-DD HH:mm:ss');
    for (var i = 0; i < data.length; i++) {
        data[i]['time']=currentTime;
    }
    var dataTitle=[];
    req.getConnection(function(err, conn) {
    // 人员分配比例
        conn.query('select * from users where company=?',[companyid], function(err,result) {
            var userList=result;
            var proportion=0;
            var curentBusinessLength=0;
            var userBusinessLength=0;
            for (var i = 0; i < userList.length; i++) {
                proportion=userList[i]['input_proportion'];
                userBusinessLength=Math.floor(proportion*0.01*data.length);
                if(i!=userList.length-1){
                    for(var j=curentBusinessLength;j<(curentBusinessLength+userBusinessLength);j++){
                        data[j]['consultant_id']=userList[i]['id'];
                        data[j]['branch_company_id']=companyid;
                    }
                    curentBusinessLength+=userBusinessLength;
                }else{
                    for(var j=curentBusinessLength;j<data.length;j++){
                        data[j]['consultant_id']=userList[i]['id'];
                        data[j]['branch_company_id']=companyid;
                    }
                }
            }
            // 拼接sql字符串
            var sql="(";
            var lastObj="";
            for (var prop in data[0]){
                lastObj=prop;
                dataTitle.push(prop);
            }
            for (var prop in data[0])
            {
                sql+=prop;
                if(prop!=lastObj){
                    sql+=" , ";
                }else{
                    sql+=") values ";
                }
            }
            for (var i = 0; i < data.length; i++) {
                sql+="(";
                for (var prop in dataTitle)
                {
                    if(!data[i][dataTitle[prop]]){
                        sql+="' '";
                    }else{
                        sql+="'"+data[i][dataTitle[prop]]+"'";
                    }
                    if(dataTitle[prop]!=lastObj){
                        sql+=",";
                    }
                }
                if(i!=data.length-1){
                    sql+="),";
                }else{
                    sql+=");";
                }
            }
            conn.query('insert ignore into business '+sql, function(err,result) {
                if (err) {
                    res.json(false);
                } else {
                    res.json(true); 
                }
            });
        }); 
    });
};

// 获取商机列表
exports.getBusinessList = function(req, res) {
    var data = {};
    data=req.body;
    var sql="";
    if(JSON.stringify(data) != "{}"){
        sql="where ";
        var lastObj="";
        for (var prop in data){
            lastObj=prop;
        }
        for (var prop in data)
        {
            sql+=" "+prop+"='"+data[prop]+"' ";
            if(prop!=lastObj){
                sql+=" AND ";
            }
        }
    }
    sql+=" ORDER BY stick DESC, time DESC";
    req.getConnection(function(err, conn) {
        if (err) {
            return res.send(401);
        } else {
            var userList=[];
            var companyList=[];
            conn.query('select * from company ', function(err,result) {
                companyList=result;
                conn.query('select * from users ', function(err,result) {
                    userList=result;
                    conn.query('select * from business '+sql, function(err,result) {
                        if (err) {
                            return next(err);
                        } else {
                            for (var i = 0; i < result.length; i++) {
                                for (var j = 0; j < userList.length; j++) {
                                    if(userList[j].id==result[i].consultant_id){
                                        result[i].consultant_name=userList[j].name;
                                    }
                                    if(userList[j].id==result[i].channel_manager_id){
                                        result[i].channel_manager_name=userList[j].name;
                                    }
                                }
                                for (var j = 0; j < companyList.length; j++) {
                                    if(companyList[j].id==result[i].branch_company_id){
                                        result[i].branch_company=companyList[j].company_name;
                                    }
                                }
                            }
                            res.json(result);
                        }
                    });
                });
            });
        }
    });
};

exports.getBusinessById = function(req, res) {
    var id=req.body.id;

    req.getConnection(function(err, conn) {
        if (err) {
            return res.send(401);
        } else {
            conn.query('select * from business WHERE id = '+id, function(err,result) {
                if (err) {
                    return res.json(false);
                } else {
                    res.json(result);
                }
            });
        }
    });
};

exports.getSalesRank = function(req, res) {
    var data = {};
    data=req.body;
    var time=data['time'];
    delete data['time'];
    var sql="";
    if(JSON.stringify(data) != "{}"){
        sql="where ";
        var lastObj="";
        for (var prop in data){
            lastObj=prop;
        }
        for (var prop in data)
        {

            sql+=" "+prop+"='"+data[prop]+"' ";
            if(prop!=lastObj){                
                sql+=" AND ";
            }

        }
    }
    // console.log(sql);
    // console.log(moment(time, "YYYY-MM").subtract(1,'d'));
    // console.log(moment('2010-01-20').isAfter('2010-01-22', 'month'));
    // moment('2010-10-20').isBefore('2010-12-31', 'month');
    req.getConnection(function(err, conn) {
        if (err) {
            return res.send(401);
        } else {
            var userList=[];
            var companyList=[];
            // 用联表查询效率更好
            conn.query('select * from company ', function(err,result) {
                companyList=result;
                conn.query('select * from users ', function(err,result) {
                    userList=result;
                    conn.query('select * from business '+sql, function(err,result) {
                        if (err) {
                            return res.send(401);
                        } else {
                            for (var i = 0; i < result.length; i++) {
                                for (var j = 0; j < userList.length; j++) {
                                    if(userList[j].id==result[i].consultant_id){
                                        result[i].consultant_name=userList[j].name;
                                    }
                                    if(userList[j].id==result[i].channel_manager_id){
                                        result[i].channel_manager_name=userList[j].name;
                                    }
                                }
                                for (var j = 0; j < companyList.length; j++) {
                                    if(companyList[j].id==result[i].branch_company_id){
                                        result[i].branch_company=companyList[j].company_name;
                                    }
                                }
                            }
                            var resData=[];
                            var tempbusinessData=[];
                            var tempData={};
                            for(var j = 0; j < companyList.length; j++){
                                tempbusinessData=[];
                                for (var i = 0; i < result.length; i++) {
                                    if(result[i]['branch_company_id']==companyList[j].id){
                                        if(moment(time, "YYYY-MM").add(1,'M').isAfter(result[i]['time'], "YYYY-MM-DD") && moment(time, "YYYY-MM").subtract(1,'d').isBefore(result[i]['time'], "YYYY-MM-DD")){
                                            tempbusinessData.push(result[i]);
                                        }
                                    }
                                }
                                tempData=companyList[j];
                                // 实际业绩金额
                                tempData['reality_amount']=0;
                                // 商机量
                                tempData['month_business_num']=tempbusinessData.length;
                                // 上门量
                                tempData['month_business_visit_num']=0;
                                // 上门率
                                tempData['visit_num_ratio']=0;
                                // 签约量
                                tempData['month_business_sign_num']=0;
                                // 报到量
                                tempData['month_business_registration_num']=0;
                                // 报到率
                                tempData['registration_num_ratio']=0;
                                // 待成交
                                tempData['month_business_wait_deal_num']=0;
                                // 成交量
                                tempData['month_business_deal_num']=0;
                                // 报到成交率
                                tempData['deal_num_ratio']=0;
                                // 商机成交率
                                tempData['business_deal_ratio']=0;
                                // 退费量
                                tempData['month_business_return_premium_num']=0;
                                // 业绩金额
                                tempData['results_amount']=0;
                                // 退费金额
                                tempData['all_return_premium']=0;
                                // 计提金额
                                tempData['all_sales_commissions']=0;
                                // console.log(tempbusinessData.length);
                                for(var i=0;i<tempbusinessData.length;i++){
                                    // 统计商机上门量
                                    if(moment(time, "YYYY-MM").add(1,'M').isAfter(result[i]['visit_time'], "YYYY-MM-DD") && moment(time, "YYYY-MM").subtract(1,'d').isBefore(result[i]['visit_time'], "YYYY-MM-DD")){
                                        tempData['month_business_visit_num']++;
                                    }
                                    // 统计商机签约
                                    if(moment(time, "YYYY-MM").add(1,'M').isAfter(result[i]['sign_time'], "YYYY-MM-DD") && moment(time, "YYYY-MM").subtract(1,'d').isBefore(result[i]['sign_time'], "YYYY-MM-DD")){
                                        tempData['month_business_sign_num']++;
                                    }
                                    // 统计商机报道
                                    if(moment(time, "YYYY-MM").add(1,'M').isAfter(result[i]['registration_time'], "YYYY-MM-DD") && moment(time, "YYYY-MM").subtract(1,'d').isBefore(result[i]['registration_time'], "YYYY-MM-DD")){
                                        tempData['month_business_registration_num']++;
                                    }
                                    // 报到量
                                    if(moment(time, "YYYY-MM").add(1,'M').isAfter(result[i]['deal_time'], "YYYY-MM-DD") && moment(time, "YYYY-MM").subtract(1,'d').isBefore(result[i]['deal_time'], "YYYY-MM-DD")){
                                        tempData['month_business_deal_num']++;
                                    }
                                }
                                // 上门率
                                if(tempData['month_business_num']!=0){
                                    tempData['visit_num_ratio']=(parseInt(tempData['month_business_visit_num']) * 100 /parseInt(tempData['month_business_num'])).toFixed(2)+'%';
                                }
                                // 报到率
                                if(tempData['month_business_sign_num']!=0){
                                    tempData['registration_num_ratio']=(parseInt(tempData['month_business_registration_num']) * 100 /parseInt(tempData['month_business_sign_num'])).toFixed(2)+'%';
                                }
                                // 报到成交率
                                if(tempData['month_business_sign_num']!=0){
                                    tempData['deal_num_ratio']=(parseInt(tempData['month_business_deal_num']) * 100 /parseInt(tempData['month_business_registration_num'])).toFixed(2)+'%';
                                }
                                resData.push(tempData);
                            }
                            res.json(resData);
                        }
                    });
                });
            });
        }
    });
};

/**
 * setMultiterm businessList
 */
exports.setMultiterm = function(req, res) {
    var data = [];
    data=JSON.parse(req.body.jsonStr);
    var itemData=[];
    for(var item in data[0]){
        if(item!="id"){
            itemData.push(item);
        }
    }
    var sql="UPDATE business SET ";
    var lastObj="";
    itemData.forEach(function(val,index){
        lastObj=val;
    });
    itemData.forEach(function(itemVal,itemIndex){
        sql+=" "+itemVal+"= CASE id";
        data.forEach(function(val,index){
            sql+=" WHEN "+val.id+" THEN '"+val[itemVal]+"'";
        });
        if(itemIndex!=itemData.length-1){
            sql+=" END,";
        }else{
            sql+=" END ";
        }
    });
    sql+="WHERE id IN ("
    data.forEach(function(val,index){
        if(index!=data.length-1){
            sql+=val.id+",";
        }else{
            sql+=val.id+")";
        }
    });
    console.log(sql);
    req.getConnection(function(err, conn) {
        if (err) {
            return res.send(401);
        } else {
            conn.query(sql, function(err,result) {
                if (err) {
                    res.json(false);
                } else {
                    res.json(true);
                }
            });
        }
    });
};